Option Explicit

Private Const TEST_TABLE_NAME As String = "table1"
Private Const TEST_POINT As String = "."
Private Const TEST_COL_NAME As String = "col1"
Private Const TEST_SHARE_LINE_COMMENT As String = "--aaaaaa"
Private Const TEST_SINGLE_LINE_COMMENT As String = "--bbbbb"

' ======================================================================
' 执行所有测试用例
' 这里只有格式化器的解析器中的 sql 作用域对象解析方法的测试
' ======================================================================
Public Sub test_all_case()
    ' Dim startTime As Date
    ' Dim endTime As Date
    ' startTime = Now

    ' 测试: 解析 sql 末尾不需要分号
    test_analyze_not_need_sqlEndSemicolon

    ' 测试: 解析 sql 字符串 + 无对齐
    test_analyze_no_alignment

    ' 测试: 解析 sql 字符串 + 每个条件分组内对齐 + 以条件运算符的右侧为基准进行对齐
    test_analyze_alignment_internal_localtion_right

    ' 测试: 解析 sql 字符串 + 每个条件分组内对齐 + 以条件运算符的左侧为基准进行对齐
    test_analyze_alignment_internal_localtion_left

    ' 测试: 解析 sql 字符串 + 每个条件分组全体对齐 + 以条件运算符的右侧为基准进行对齐
    test_analyze_alignment_all_localtion_right

    ' 测试: 解析 sql 字符串 + 每个条件分组全体对齐 + 以条件运算符的左侧为基准进行对齐
    test_analyze_alignment_all_localtion_left

    ' 测试: 解析 sql 字符串 + 正常连接多行注释 + 各部分左侧的注释
    test_analyze_with_multiline_comment_and_section_left_comment

    ' 测试: 解析 update 的基础结构
    test_analyze_update_no_alignment
    ' 测试: 解析 update 的基础结构 + 每个条件分组全体对齐 + 以条件运算符的右侧为基准进行对齐
    test_analyze_update_all_localtion_right
    ' 测试: 解析 update 的基础结构 + 每个条件分组全体对齐 + 以条件运算符的左侧为基准进行对齐
    test_analyze_update_all_localtion_left

    ' 测试: 解析 delete 的基础结构
    test_analyze_delete_no_alignment
    ' 测试: 解析 delete 的基础结构 + 每个条件分组全体对齐 + 以条件运算符的右侧为基准进行对齐
    test_analyze_delete_all_localtion_right
    ' 测试: 解析 delete 的基础结构 + 每个条件分组全体对齐 + 以条件运算符的左侧为基准进行对齐
    test_analyze_delete_all_localtion_left

    ' 测试: 解析 insert 的基础结构
    test_analyze_insert_no_alignment
    ' 测试: 解析 insert 的基础结构 + 每个条件分组全体对齐 + 以条件运算符的右侧为基准进行对齐
    test_analyze_insert_all_localtion_right
    ' 测试: 解析 insert 的基础结构 + 每个条件分组全体对齐 + 以条件运算符的左侧为基准进行对齐
    test_analyze_insert_all_localtion_left
    ' 测试: 解析 insert 的基础结构 + 列的逗号后面不需要换行
    test_analyze_insert_colList_not_lineBreakAfterComma

    ' endTime = Now
    ' Debug.Print "Test___SqlFormatter.vb execution time = " & DateDiff("s", startTime, endTime) & "s" & vbCrLf

    ' 清除池数据
    UtilsPool.clearAllPool
End Sub

' ======================================================================
' Sql 解析方法的测试
' ======================================================================

' 测试: 解析 sql 末尾不需要分号
Private Sub test_analyze_not_need_sqlEndSemicolon()
    Dim config As SqlFormatterConfiguration
    Set config = New SqlFormatterConfiguration
    config.alignmentLevel = SqlAlignmentLevel.NO_ALIGNMENT
    config.needSqlEndSemicolon = False

    Dim formatter As SqlFormatter
    Set formatter = New SqlFormatter
    formatter.Init config

    Dim token As SqlToken
    Dim scanner As ObjArrayRefScanner
    Dim elem As ISqlFormatterElem
    Dim strArr As IPrimitiveArrayRef
    Dim sqlStr As String
    Dim sqlEnvs As IObjArrayRef

    ' ==============================
    ' 1. 只有一个sql，末尾没有分号、换行符
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = "select x1 from x2;"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    ' 3. 检查结果
    Debug.Assert strArr.Count = 4
    Debug.Assert strArr.Item(0)  = "select"
    Debug.Assert strArr.Item(1)  = "    x1"
    Debug.Assert strArr.Item(2)  = "from"
    Debug.Assert strArr.Item(3)  = "    x2"

    Debug.Assert formatter.getSqlLineSize = 4

    ' ==============================
    ' 2. 有两个sql，只有第一个sql末尾有换行
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = "select x1 from x2;select x3 from x4"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    ' 3. 检查结果
    Debug.Assert strArr.Count = 9
    Debug.Assert strArr.Item(0)  = "select"
    Debug.Assert strArr.Item(1)  = "    x1"
    Debug.Assert strArr.Item(2)  = "from"
    Debug.Assert strArr.Item(3)  = "    x2"
    Debug.Assert strArr.Item(4)  = ""
    Debug.Assert strArr.Item(5)  = "select"
    Debug.Assert strArr.Item(6)  = "    x3"
    Debug.Assert strArr.Item(7)  = "from"
    Debug.Assert strArr.Item(8)  = "    x4"

    Debug.Assert formatter.getSqlLineSize = 9

    ' ==============================
    ' 3. 有三个sql，只有第一个、第二个sql末尾有换行
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = "select x1 from x2;select x3 from x4; select x5 from x6"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    ' 3. 检查结果
    Debug.Assert strArr.Count = 14
    Debug.Assert strArr.Item(0)  = "select"
    Debug.Assert strArr.Item(1)  = "    x1"
    Debug.Assert strArr.Item(2)  = "from"
    Debug.Assert strArr.Item(3)  = "    x2"
    Debug.Assert strArr.Item(4)  = ""
    Debug.Assert strArr.Item(5)  = "select"
    Debug.Assert strArr.Item(6)  = "    x3"
    Debug.Assert strArr.Item(7)  = "from"
    Debug.Assert strArr.Item(8)  = "    x4"
    Debug.Assert strArr.Item(9)  = ""
    Debug.Assert strArr.Item(10) = "select"
    Debug.Assert strArr.Item(11) = "    x5"
    Debug.Assert strArr.Item(12) = "from"
    Debug.Assert strArr.Item(13) = "    x6"

    Debug.Assert formatter.getSqlLineSize = 14
End Sub

' 测试: 解析 sql 字符串 + 无对齐
Private Sub test_analyze_no_alignment()
    Dim config As SqlFormatterConfiguration
    Set config = New SqlFormatterConfiguration
    config.alignmentLevel = SqlAlignmentLevel.NO_ALIGNMENT

    Dim formatter As SqlFormatter
    Set formatter = New SqlFormatter
    formatter.Init config

    Dim token As SqlToken
    Dim scanner As ObjArrayRefScanner
    Dim elem As ISqlFormatterElem
    Dim strArr As IPrimitiveArrayRef
    Dim sqlStr As String
    Dim sqlEnvs As IObjArrayRef

    ' ==============================
    ' 1. 测试: 所有 token 都被使用，并解析成一个作用域
    '    select + STR_NAME
    '    + from + STR_NAME
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = "select x1 from x2"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    ' 3. 检查结果
    Debug.Assert strArr.Count = 4
    Debug.Assert strArr.Item(0)  = "select"
    Debug.Assert strArr.Item(1)  = "    x1"
    Debug.Assert strArr.Item(2)  = "from"
    Debug.Assert strArr.Item(3)  = "    x2;"

    ' ==============================
    ' 2. 测试: 所有 token 都被使用，并解析成一个作用域 + 作用域以 [;] 结尾
    '    select + STR_NAME
    '    + from + STR_NAME + ;
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = "select x1 from x2;"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    ' 3. 检查结果
    Debug.Assert strArr.Count = 4
    Debug.Assert strArr.Item(0)  = "select"
    Debug.Assert strArr.Item(1)  = "    x1"
    Debug.Assert strArr.Item(2)  = "from"
    Debug.Assert strArr.Item(3)  = "    x2;"

    Debug.Assert formatter.getSqlLineSize = 4

    ' 4. 再次执行，来测试 sql 字符串
    formatter.analyze sqlStr
    Debug.Assert formatter.getSql = _
        "select" & vbCrLf & _
        "    x1" & vbCrLf & _
        "from" & vbCrLf & _
        "    x2;"

    ' ==============================
    ' 3. 测试: 所有 token 都被使用 + 多个作用域 + 最后不是以 [;] 结尾
    '    select + STR_NAME
    '    + from + STR_NAME + ;
    '    + select + STR_NAME
    '    + from + STR_NAME
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = "select x1 from x2;select x3 from x4"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    ' 3. 检查结果
    Debug.Assert strArr.Count = 8
    Debug.Assert strArr.Item(0)  = "select"
    Debug.Assert strArr.Item(1)  = "    x1"
    Debug.Assert strArr.Item(2)  = "from"
    Debug.Assert strArr.Item(3)  = "    x2;"
    Debug.Assert strArr.Item(4)  = "select"
    Debug.Assert strArr.Item(5)  = "    x3"
    Debug.Assert strArr.Item(6)  = "from"
    Debug.Assert strArr.Item(7)  = "    x4;"

    Debug.Assert formatter.getSqlLineSize = 8

    ' 4. 再次执行，来测试 sql 字符串
    formatter.analyze sqlStr
    Debug.Assert formatter.getSql = _
        "select" & vbCrLf & _
        "    x1" & vbCrLf & _
        "from" & vbCrLf & _
        "    x2;" & vbCrLf & _
        "select" & vbCrLf & _
        "    x3" & vbCrLf & _
        "from" & vbCrLf & _
        "    x4;"

    ' ==============================
    ' 4. 测试: 所有 token 都被使用 + 多个作用域 + 最后不是以 [;] 结尾
    '    select + STR_NAME
    '    + from + STR_NAME + ;
    '    + select + STR_NAME
    '    + from + STR_NAME + ;
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = "select x1 from x2;select x3 from x4;"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    ' 3. 检查结果
    Debug.Assert strArr.Count = 8
    Debug.Assert strArr.Item(0)  = "select"
    Debug.Assert strArr.Item(1)  = "    x1"
    Debug.Assert strArr.Item(2)  = "from"
    Debug.Assert strArr.Item(3)  = "    x2;"
    Debug.Assert strArr.Item(4)  = "select"
    Debug.Assert strArr.Item(5)  = "    x3"
    Debug.Assert strArr.Item(6)  = "from"
    Debug.Assert strArr.Item(7)  = "    x4;"

    Debug.Assert formatter.getSqlLineSize = 8

    ' 4. 再次执行，来测试 sql 字符串
    formatter.analyze sqlStr
    Debug.Assert formatter.getSql = _
        "select" & vbCrLf & _
        "    x1" & vbCrLf & _
        "from"   & vbCrLf & _
        "    x2;" & vbCrLf & _
        "select" & vbCrLf & _
        "    x3" & vbCrLf & _
        "from"   & vbCrLf & _
        "    x4;"

    ' ==============================
    ' 5. 测试: 一个 select 作用域 + STR_NAME (使用兜底方法解析) + 一个 select 作用域
    '    select + STR_NAME
    '    + from + STR_NAME + where a < b and c between x to y;
    '    + STR_NAME + ;
    '    + select + STR_NAME
    '    + from + STR_NAME
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = "select x1 from x2 where a < b and c between x and y;abc;select x3 from x4"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    ' 3. 检查结果
    Debug.Assert strArr.Count = 12
    Debug.Assert strArr.Item(0)  = "select"
    Debug.Assert strArr.Item(1)  = "    x1"
    Debug.Assert strArr.Item(2)  = "from"
    Debug.Assert strArr.Item(3)  = "    x2"
    Debug.Assert strArr.Item(4)  = "where"
    Debug.Assert strArr.Item(5)  = "        a < b"
    Debug.Assert strArr.Item(6)  = "    and c between x and y;"
    Debug.Assert strArr.Item(7)  = "abc;"
    Debug.Assert strArr.Item(8)  = "select"
    Debug.Assert strArr.Item(9)  = "    x3"
    Debug.Assert strArr.Item(10) = "from"
    Debug.Assert strArr.Item(11) = "    x4;"

    Debug.Assert formatter.getSqlLineSize = 12

    ' 4. 再次执行，来测试 sql 字符串
    formatter.analyze sqlStr
    Debug.Assert formatter.getSql = _
        "select" & vbCrLf & _
        "    x1" & vbCrLf & _
        "from" & vbCrLf & _
        "    x2" & vbCrLf & _
        "where" & vbCrLf & _
        "        a < b" & vbCrLf & _
        "    and c between x and y;" & vbCrLf & _
        "abc;" & vbCrLf & _
        "select" & vbCrLf & _
        "    x3" & vbCrLf & _
        "from" & vbCrLf & _
        "    x4;"

    ' ==============================
    ' 6. 测试: 一个 select 作用域 + STR_NAME (使用兜底方法解析) + 一个 select 作用域
    '         + 能够解析注释
    '    select + STR_NAME
    '    + from + STR_NAME + 一个注释 + ;
    '    + STR_NAME + 一个注释 + ;
    '    + select + STR_NAME + 一个注释
    '    + from + STR_NAME
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = "select x1 from x2 --c1" & vbCrLf & _
             ";" & vbCrLf & _
             "abc --c2" & vbCrLf & _
             ";" & vbCrLf & _
             "select x3 --c3" & vbCrLf & _
             "from x4"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    ' 3. 检查结果
    Debug.Assert strArr.Count = 11
    Debug.Assert strArr.Item(0)  = "select"
    Debug.Assert strArr.Item(1)  = "    x1"
    Debug.Assert strArr.Item(2)  = "from"
    Debug.Assert strArr.Item(3)  = "    x2 --c1"
    Debug.Assert strArr.Item(4)  = ";"
    Debug.Assert strArr.Item(5)  = "abc --c2"
    Debug.Assert strArr.Item(6)  = ";"
    Debug.Assert strArr.Item(7)  = "select"
    Debug.Assert strArr.Item(8)  = "    x3 --c3"
    Debug.Assert strArr.Item(9)  = "from"
    Debug.Assert strArr.Item(10) = "    x4;"

    Debug.Assert formatter.getSqlLineSize = 11

    ' 4. 再次执行，来测试 sql 字符串
    formatter.analyze sqlStr
    Debug.Assert formatter.getSql = _
        "select" & vbCrLf & _
        "    x1" & vbCrLf & _
        "from" & vbCrLf & _
        "    x2 --c1" & vbCrLf & _
        ";" & vbCrLf & _
        "abc --c2" & vbCrLf & _
        ";" & vbCrLf & _
        "select" & vbCrLf & _
        "    x3 --c3" & vbCrLf & _
        "from" & vbCrLf & _
        "    x4;"

    ' ==============================
    ' 7. 测试: 空字符串，返回 null
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = ""

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    ' 3. 检查结果
    Debug.Assert strArr Is Nothing

    ' 4. 再次执行，来测试 sql 字符串
    formatter.analyze sqlStr
    Debug.Assert formatter.getSql = ""

    Debug.Assert formatter.getSqlLineSize = 0
End Sub

' 测试: 解析 sql 字符串 + select 的基础 sql 组成部分测试 + 每个条件分组内对齐 + 以条件运算符的右侧为基准进行对齐
Private Sub test_analyze_alignment_internal_localtion_right()
    Dim config As SqlFormatterConfiguration
    Set config = New SqlFormatterConfiguration
    ' config.alignmentLevel = SqlAlignmentLevel.INTERNAL

    Dim formatter As SqlFormatter
    Set formatter = New SqlFormatter
    formatter.Init config

    Dim token As SqlToken
    Dim scanner As ObjArrayRefScanner
    Dim elem As ISqlFormatterElem
    Dim strArr As IPrimitiveArrayRef
    Dim sqlStr As String
    Dim sqlEnvs As IObjArrayRef

    ' ==============================
    ' 1. 测试: 所有 token 都被使用，并解析成一个作用域
    '    select + STR_NAME
    '    + from + STR_NAME
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = _
        "select x1" & vbCrLf & _
        "from" & vbCrLf & _
        "    (" & vbCrLf & _
        "        select *" & vbCrLf & _
        "        from table1 t1" & vbCrLf & _
        "        where" & vbCrLf & _
        "            a < b" & vbCrLf & _
        "            and a <= c" & vbCrLf & _
        "    ) as tx"
    sqlStr = sqlStr & vbCrLf & _
        "left join table2 t2" & vbCrLf & _
        "on t1.aa = t2.a1" & vbCrLf & _
        "and t1.bb <= t2.b1" & vbCrLf & _
        "and t1.cc = t2.c1"
    sqlStr = sqlStr & vbCrLf & _
        "where" & vbCrLf & _
        "    a < b" & vbCrLf & _
        "    and aa <= b" & vbCrLf & _
        "    and aaa <= case" & vbCrLf & _
        "            when y then b" & vbCrLf & _
        "            else c" & vbCrLf & _
        "            end" & vbCrLf & _
        "    and aaaa between x and y" & vbCrLf & _
        "    and aa in (z1, z2, z3)"
    sqlStr = sqlStr & vbCrLf & _
        "    and case" & vbCrLf & _
        "        when y then b" & vbCrLf & _
        "        else c" & vbCrLf & _
        "    end" & vbCrLf & _
        "    <= case" & vbCrLf & _
        "        when y then b" & vbCrLf & _
        "        else c" & vbCrLf & _
        "    end"
    sqlStr = sqlStr & vbCrLf & _
        "and not exists (" & vbCrLf & _
        "    select *" & vbCrLf & _
        "    from " & vbCrLf & _
        "        (" & vbCrLf & _
        "            select *" & vbCrLf & _
        "            from table1 t1" & vbCrLf & _
        "            where" & vbCrLf & _
        "                a < b" & vbCrLf & _
        "                and a <= c" & vbCrLf & _
        "        ) as tx" & vbCrLf & _
        "    where" & vbCrLf & _
        "        a < b" & vbCrLf & _
        "        and aa <= c" & vbCrLf & _
        ")"
    sqlStr = sqlStr & vbCrLf & _
        "order by t1.aa asc, t1.bb desc, t2.c1, t2.d1 desc" & vbCrLf & _
        "group by t1.aa, t1.bb, t2.c1, t2.d1"
    sqlStr = sqlStr & vbCrLf & _
        "union all" & vbCrLf & _
        "(" & vbCrLf & _
        "  select tx.a, tx.b, max(a, b, c)" & vbCrLf & _
        "  from tableX tx" & vbCrLf & _
        "  left join tableY ty" & vbCrLf & _
        "  on ty.aa = tx.a1" & vbCrLf & _
        "  and ty.bb <= tx.b1" & vbCrLf & _
        "  where tx.a = 1" & vbCrLf & _
        "  and tx.b like '%abc%'" & vbCrLf & _
        ")"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    ' 3. 检查结果
    Debug.Assert strArr.Count = 80
    Debug.Assert strArr.Item(0)  = "select"
    Debug.Assert strArr.Item(1)  = "    x1"
    Debug.Assert strArr.Item(2)  = "from"
    Debug.Assert strArr.Item(3)  = "    ("
    Debug.Assert strArr.Item(4)  = "        select"
    Debug.Assert strArr.Item(5)  = "            *"
    Debug.Assert strArr.Item(6)  = "        from"
    Debug.Assert strArr.Item(7)  = "            table1 t1"
    Debug.Assert strArr.Item(8)  = "        where"
    Debug.Assert strArr.Item(9)  = "                a  < b"
    Debug.Assert strArr.Item(10) = "            and a <= c"
    Debug.Assert strArr.Item(11) = "    ) as tx"
    Debug.Assert strArr.Item(12) = "left join"
    Debug.Assert strArr.Item(13) = "    table2 t2"
    Debug.Assert strArr.Item(14) = "on"
    Debug.Assert strArr.Item(15) = "        t1.aa  = t2.a1"
    Debug.Assert strArr.Item(16) = "    and t1.bb <= t2.b1"
    Debug.Assert strArr.Item(17) = "    and t1.cc  = t2.c1"
    Debug.Assert strArr.Item(18) = "where"
    Debug.Assert strArr.Item(19) = "        a          < b"
    Debug.Assert strArr.Item(20) = "    and aa        <= b"
    Debug.Assert strArr.Item(21) = "    and aaa       <= case"
    Debug.Assert strArr.Item(22) = "                         when y then b"
    Debug.Assert strArr.Item(23) = "                         else c"
    Debug.Assert strArr.Item(24) = "                     end"
    Debug.Assert strArr.Item(25) = "    and aaaa between x and y"
    Debug.Assert strArr.Item(26) = "    and aa        in (z1, z2, z3)"
    Debug.Assert strArr.Item(27) = "    and case"
    Debug.Assert strArr.Item(28) = "            when y then b"
    Debug.Assert strArr.Item(29) = "            else c"
    Debug.Assert strArr.Item(30) = "        end"
    Debug.Assert strArr.Item(31) = "        <="
    Debug.Assert strArr.Item(32) = "        case"
    Debug.Assert strArr.Item(33) = "            when y then b"
    Debug.Assert strArr.Item(34) = "            else c"
    Debug.Assert strArr.Item(35) = "        end"
    Debug.Assert strArr.Item(36) = "    and not exists ("
    Debug.Assert strArr.Item(37) = "            select"
    Debug.Assert strArr.Item(38) = "                *"
    Debug.Assert strArr.Item(39) = "            from"
    Debug.Assert strArr.Item(40) = "                ("
    Debug.Assert strArr.Item(41) = "                    select"
    Debug.Assert strArr.Item(42) = "                        *"
    Debug.Assert strArr.Item(43) = "                    from"
    Debug.Assert strArr.Item(44) = "                        table1 t1"
    Debug.Assert strArr.Item(45) = "                    where"
    Debug.Assert strArr.Item(46) = "                            a  < b"
    Debug.Assert strArr.Item(47) = "                        and a <= c"
    Debug.Assert strArr.Item(48) = "                ) as tx"
    Debug.Assert strArr.Item(49) = "            where"
    Debug.Assert strArr.Item(50) = "                    a   < b"
    Debug.Assert strArr.Item(51) = "                and aa <= c"
    Debug.Assert strArr.Item(52) = "        )"
    Debug.Assert strArr.Item(53) = "order by"
    Debug.Assert strArr.Item(54) = "    t1.aa asc,"
    Debug.Assert strArr.Item(55) = "    t1.bb desc,"
    Debug.Assert strArr.Item(56) = "    t2.c1,"
    Debug.Assert strArr.Item(57) = "    t2.d1 desc"
    Debug.Assert strArr.Item(58) = "group by"
    Debug.Assert strArr.Item(59) = "    t1.aa,"
    Debug.Assert strArr.Item(60) = "    t1.bb,"
    Debug.Assert strArr.Item(61) = "    t2.c1,"
    Debug.Assert strArr.Item(62) = "    t2.d1"
    Debug.Assert strArr.Item(63) = "union all"
    Debug.Assert strArr.Item(64) = "("
    Debug.Assert strArr.Item(65) = "    select"
    Debug.Assert strArr.Item(66) = "        tx.a,"
    Debug.Assert strArr.Item(67) = "        tx.b,"
    Debug.Assert strArr.Item(68) = "        max(a, b, c)"
    Debug.Assert strArr.Item(69) = "    from"
    Debug.Assert strArr.Item(70) = "        tableX tx"
    Debug.Assert strArr.Item(71) = "    left join"
    Debug.Assert strArr.Item(72) = "        tableY ty"
    Debug.Assert strArr.Item(73) = "    on"
    Debug.Assert strArr.Item(74) = "            ty.aa  = tx.a1"
    Debug.Assert strArr.Item(75) = "        and ty.bb <= tx.b1"
    Debug.Assert strArr.Item(76) = "    where"
    Debug.Assert strArr.Item(77) = "            tx.a    = 1"
    Debug.Assert strArr.Item(78) = "        and tx.b like '%abc%'"
    Debug.Assert strArr.Item(79) = ");"

    Debug.Assert formatter.getSqlLineSize = 80

End Sub

' 测试: 解析 sql 字符串 + 每个条件分组内对齐 + 以条件运算符的左侧为基准进行对齐
Private Sub test_analyze_alignment_internal_localtion_left()
    Dim config As SqlFormatterConfiguration
    Set config = New SqlFormatterConfiguration
    ' config.alignmentLevel = SqlAlignmentLevel.INTERNAL
    config.alignmentPosition = SqlAlignmentPosition.OPERATOR_LEFT

    Dim formatter As SqlFormatter
    Set formatter = New SqlFormatter
    formatter.Init config

    Dim token As SqlToken
    Dim scanner As ObjArrayRefScanner
    Dim elem As ISqlFormatterElem
    Dim strArr As IPrimitiveArrayRef
    Dim sqlStr As String
    Dim sqlEnvs As IObjArrayRef

    ' ==============================
    ' 1. 测试: 所有 token 都被使用，并解析成一个作用域
    '    select + STR_NAME
    '    + from + STR_NAME
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = _
        "select x1" & vbCrLf & _
        "from" & vbCrLf & _
        "    (" & vbCrLf & _
        "        select *" & vbCrLf & _
        "        from table1 t1" & vbCrLf & _
        "        where" & vbCrLf & _
        "            a < b" & vbCrLf & _
        "            and a <= c" & vbCrLf & _
        "    ) as tx" & vbCrLf & _
        "where" & vbCrLf & _
        "    a < b" & vbCrLf & _
        "    and aa <= b" & vbCrLf & _
        "    and aaa <= case" & vbCrLf & _
        "            when y then b" & vbCrLf & _
        "            else c" & vbCrLf & _
        "            end" & vbCrLf & _
        "    and aaaa between x and y" & vbCrLf & _
        "    and aa in (z1, z2, z3)"
    sqlStr = sqlStr & vbCrLf & _
        "    and case" & vbCrLf & _
        "        when y then b" & vbCrLf & _
        "        else c" & vbCrLf & _
        "    end" & vbCrLf & _
        "    <= case" & vbCrLf & _
        "        when y then b" & vbCrLf & _
        "        else c" & vbCrLf & _
        "    end"
    sqlStr = sqlStr & vbCrLf & _
        "and not exists (" & vbCrLf & _
        "    select *" & vbCrLf & _
        "    from " & vbCrLf & _
        "        (" & vbCrLf & _
        "            select *" & vbCrLf & _
        "            from table1 t1" & vbCrLf & _
        "            where" & vbCrLf & _
        "                a < b" & vbCrLf & _
        "                and a <= c" & vbCrLf & _
        "        ) as tx" & vbCrLf & _
        "    where" & vbCrLf & _
        "        a < b" & vbCrLf & _
        "        and aa <= c" & vbCrLf & _
        ")"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    ' 3. 检查结果
    Debug.Assert strArr.Count = 47
    Debug.Assert strArr.Item(0)  = "select"
    Debug.Assert strArr.Item(1)  = "    x1"
    Debug.Assert strArr.Item(2)  = "from"
    Debug.Assert strArr.Item(3)  = "    ("
    Debug.Assert strArr.Item(4)  = "        select"
    Debug.Assert strArr.Item(5)  = "            *"
    Debug.Assert strArr.Item(6)  = "        from"
    Debug.Assert strArr.Item(7)  = "            table1 t1"
    Debug.Assert strArr.Item(8)  = "        where"
    Debug.Assert strArr.Item(9)  = "                a < b"
    Debug.Assert strArr.Item(10) = "            and a <= c"
    Debug.Assert strArr.Item(11) = "    ) as tx"
    Debug.Assert strArr.Item(12) = "where"
    Debug.Assert strArr.Item(13) = "        a    < b"
    Debug.Assert strArr.Item(14) = "    and aa   <= b"
    Debug.Assert strArr.Item(15) = "    and aaa  <= case"
    Debug.Assert strArr.Item(16) = "                    when y then b"
    Debug.Assert strArr.Item(17) = "                    else c"
    Debug.Assert strArr.Item(18) = "                end"
    Debug.Assert strArr.Item(19) = "    and aaaa between x and y"
    Debug.Assert strArr.Item(20) = "    and aa   in (z1, z2, z3)"
    Debug.Assert strArr.Item(21) = "    and case"
    Debug.Assert strArr.Item(22) = "            when y then b"
    Debug.Assert strArr.Item(23) = "            else c"
    Debug.Assert strArr.Item(24) = "        end"
    Debug.Assert strArr.Item(25) = "        <="
    Debug.Assert strArr.Item(26) = "        case"
    Debug.Assert strArr.Item(27) = "            when y then b"
    Debug.Assert strArr.Item(28) = "            else c"
    Debug.Assert strArr.Item(29) = "        end"
    Debug.Assert strArr.Item(30) = "    and not exists ("
    Debug.Assert strArr.Item(31) = "            select"
    Debug.Assert strArr.Item(32) = "                *"
    Debug.Assert strArr.Item(33) = "            from"
    Debug.Assert strArr.Item(34) = "                ("
    Debug.Assert strArr.Item(35) = "                    select"
    Debug.Assert strArr.Item(36) = "                        *"
    Debug.Assert strArr.Item(37) = "                    from"
    Debug.Assert strArr.Item(38) = "                        table1 t1"
    Debug.Assert strArr.Item(39) = "                    where"
    Debug.Assert strArr.Item(40) = "                            a < b"
    Debug.Assert strArr.Item(41) = "                        and a <= c"
    Debug.Assert strArr.Item(42) = "                ) as tx"
    Debug.Assert strArr.Item(43) = "            where"
    Debug.Assert strArr.Item(44) = "                    a  < b"
    Debug.Assert strArr.Item(45) = "                and aa <= c"
    Debug.Assert strArr.Item(46) = "        );"

    Debug.Assert formatter.getSqlLineSize = 47
End Sub

' 测试: 解析 sql 字符串 + 每个条件分组全体对齐 + 以条件运算符的右侧为基准进行对齐
Private Sub test_analyze_alignment_all_localtion_right()
    Dim config As SqlFormatterConfiguration
    Set config = New SqlFormatterConfiguration
    config.alignmentLevel = SqlAlignmentLevel.ALL_LEVEL

    Dim formatter As SqlFormatter
    Set formatter = New SqlFormatter
    formatter.Init config

    Dim token As SqlToken
    Dim scanner As ObjArrayRefScanner
    Dim elem As ISqlFormatterElem
    Dim strArr As IPrimitiveArrayRef
    Dim sqlStr As String
    Dim sqlEnvs As IObjArrayRef

    ' ==============================
    ' 1. 测试: 所有 token 都被使用，并解析成一个作用域
    '    select + STR_NAME
    '    + from + STR_NAME
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = _
        "with wa (wac1, wac2, wac3) as (select * from table01)," & vbCrLf & _
        "     wb (wbc1, wbc2, wbc3) as (" & vbCrLf & _
        "          select * from table01 t1" & vbCrLf & _
        "          left join table02 t2" & vbCrLf & _
        "          on t1.c1 = t2.c1" & vbCrLf & _
        "          and t1.c2 <= t2.c2" & vbCrLf & _
        "          where t1.c1 = 'xxx'" & vbCrLf & _
        "          and t2.c3 <= 1234" & vbCrLf & _
        "    )"
    sqlStr = sqlStr & vbCrLf & _
        "select x1" & vbCrLf & _
        "from" & vbCrLf & _
        "    (" & vbCrLf & _
        "        select *" & vbCrLf & _
        "        from table1 t1" & vbCrLf & _
        "        where" & vbCrLf & _
        "            aa < b" & vbCrLf & _
        "            and aaa <= c" & vbCrLf & _
        "    ) as tx" & vbCrLf & _
        "where" & vbCrLf & _
        "    a < b" & vbCrLf & _
        "    and aa <= b" & vbCrLf & _
        "    and aaa <= case" & vbCrLf & _
        "            when y then b" & vbCrLf & _
        "            else c" & vbCrLf & _
        "            end" & vbCrLf & _
        "    and aaaa between x and y" & vbCrLf & _
        "    and aa in (z1, z2, z3)"
    sqlStr = sqlStr & vbCrLf & _
        "    and case" & vbCrLf & _
        "        when y then b" & vbCrLf & _
        "        else c" & vbCrLf & _
        "    end" & vbCrLf & _
        "    <= case" & vbCrLf & _
        "        when y then b" & vbCrLf & _
        "        else c" & vbCrLf & _
        "    end"
    sqlStr = sqlStr & vbCrLf & _
        "and not exists (" & vbCrLf & _
        "    select *" & vbCrLf & _
        "    from " & vbCrLf & _
        "        (" & vbCrLf & _
        "            select *" & vbCrLf & _
        "            from table1 t1" & vbCrLf & _
        "            where" & vbCrLf & _
        "                x < b" & vbCrLf & _
        "                and x <= c" & vbCrLf & _
        "        ) as tx" & vbCrLf & _
        "    where" & vbCrLf & _
        "        a < b" & vbCrLf & _
        "        and aa <= c" & vbCrLf
    ' --- 这里测试右侧分组的对齐
    sqlStr = sqlStr & vbCrLf & _
        "        and bbb <= (" & vbCrLf & _
        "                      select *" & vbCrLf & _
        "                      where" & vbCrLf & _
        "                         g  = 1" & vbCrLf & _
        "                         and h <= 2" & vbCrLf & _
        "       )" & vbCrLf & _
        ")"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    ' 3. 检查结果
    Debug.Assert strArr.Count = 74
    Debug.Assert strArr.Item(0)  = "with wa (wac1, wac2, wac3) as ("
    Debug.Assert strArr.Item(1)  = "    select"
    Debug.Assert strArr.Item(2)  = "        *"
    Debug.Assert strArr.Item(3)  = "    from"
    Debug.Assert strArr.Item(4)  = "        table01"
    Debug.Assert strArr.Item(5)  = "),"
    Debug.Assert strArr.Item(6)  = "wb (wbc1, wbc2, wbc3) as ("
    Debug.Assert strArr.Item(7)  = "    select"
    Debug.Assert strArr.Item(8)  = "        *"
    Debug.Assert strArr.Item(9)  = "    from"
    Debug.Assert strArr.Item(10) = "        table01 t1"
    Debug.Assert strArr.Item(11) = "    left join"
    Debug.Assert strArr.Item(12) = "        table02 t2"
    Debug.Assert strArr.Item(13) = "    on"
    Debug.Assert strArr.Item(14) = "            t1.c1    = t2.c1"
    Debug.Assert strArr.Item(15) = "        and t1.c2   <= t2.c2"
    Debug.Assert strArr.Item(16) = "    where"
    Debug.Assert strArr.Item(17) = "            t1.c1    = 'xxx'"
    Debug.Assert strArr.Item(18) = "        and t2.c3   <= 1234"
    Debug.Assert strArr.Item(19) = ")"
    Debug.Assert strArr.Item(20) = "select"
    Debug.Assert strArr.Item(21) = "    x1"
    Debug.Assert strArr.Item(22) = "from"
    Debug.Assert strArr.Item(23) = "    ("
    Debug.Assert strArr.Item(24) = "        select"
    Debug.Assert strArr.Item(25) = "            *"
    Debug.Assert strArr.Item(26) = "        from"
    Debug.Assert strArr.Item(27) = "            table1 t1"
    Debug.Assert strArr.Item(28) = "        where"
    Debug.Assert strArr.Item(29) = "                aa   < b"
    Debug.Assert strArr.Item(30) = "            and aaa <= c"
    Debug.Assert strArr.Item(31) = "    ) as tx"
    Debug.Assert strArr.Item(32) = "where"
    Debug.Assert strArr.Item(33) = "        a            < b"
    Debug.Assert strArr.Item(34) = "    and aa          <= b"
    Debug.Assert strArr.Item(35) = "    and aaa         <= case"
    Debug.Assert strArr.Item(36) = "                           when y then b"
    Debug.Assert strArr.Item(37) = "                           else c"
    Debug.Assert strArr.Item(38) = "                       end"
    Debug.Assert strArr.Item(39) = "    and aaaa   between x and y"
    Debug.Assert strArr.Item(40) = "    and aa          in (z1, z2, z3)"
    Debug.Assert strArr.Item(41) = "    and case"
    Debug.Assert strArr.Item(42) = "            when y then b"
    Debug.Assert strArr.Item(43) = "            else c"
    Debug.Assert strArr.Item(44) = "        end"
    Debug.Assert strArr.Item(45) = "        <="
    Debug.Assert strArr.Item(46) = "        case"
    Debug.Assert strArr.Item(47) = "            when y then b"
    Debug.Assert strArr.Item(48) = "            else c"
    Debug.Assert strArr.Item(49) = "        end"
    Debug.Assert strArr.Item(50) = "    and not exists ("
    Debug.Assert strArr.Item(51) = "            select"
    Debug.Assert strArr.Item(52) = "                *"
    Debug.Assert strArr.Item(53) = "            from"
    Debug.Assert strArr.Item(54) = "                ("
    Debug.Assert strArr.Item(55) = "                    select"
    Debug.Assert strArr.Item(56) = "                        *"
    Debug.Assert strArr.Item(57) = "                    from"
    Debug.Assert strArr.Item(58) = "                        table1 t1"
    Debug.Assert strArr.Item(59) = "                    where"
    Debug.Assert strArr.Item(60) = "                            x  < b"
    Debug.Assert strArr.Item(61) = "                        and x <= c"
    Debug.Assert strArr.Item(62) = "                ) as tx"
    Debug.Assert strArr.Item(63) = "            where"
    Debug.Assert strArr.Item(64) = "                    a          < b"
    Debug.Assert strArr.Item(65) = "                and aa        <= c"
    Debug.Assert strArr.Item(66) = "                and bbb       <= ("
    Debug.Assert strArr.Item(67) = "                                     select"
    Debug.Assert strArr.Item(68) = "                                         *"
    Debug.Assert strArr.Item(69) = "                                     where"
    Debug.Assert strArr.Item(70) = "                                             g  = 1"
    Debug.Assert strArr.Item(71) = "                                         and h <= 2"
    Debug.Assert strArr.Item(72) = "                                 )"
    Debug.Assert strArr.Item(73) = "        );"

    Debug.Assert formatter.getSqlLineSize = 74
End Sub

' 测试: 解析 sql 字符串 + 每个条件分组全体对齐 + 以条件运算符的左侧为基准进行对齐
Private Sub test_analyze_alignment_all_localtion_left()
    Dim config As SqlFormatterConfiguration
    Set config = New SqlFormatterConfiguration
    config.alignmentLevel = SqlAlignmentLevel.ALL_LEVEL
    config.alignmentPosition = SqlAlignmentPosition.OPERATOR_LEFT

    Dim formatter As SqlFormatter
    Set formatter = New SqlFormatter
    formatter.Init config

    Dim token As SqlToken
    Dim scanner As ObjArrayRefScanner
    Dim elem As ISqlFormatterElem
    Dim strArr As IPrimitiveArrayRef
    Dim sqlStr As String
    Dim sqlEnvs As IObjArrayRef

    ' ==============================
    ' 1. 测试: 所有 token 都被使用，并解析成一个作用域
    '    select + STR_NAME
    '    + from + STR_NAME
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = _
        "select x1" & vbCrLf & _
        "from" & vbCrLf & _
        "    (" & vbCrLf & _
        "        select *" & vbCrLf & _
        "        from table1 t1" & vbCrLf & _
        "        where" & vbCrLf & _
        "            aa < b" & vbCrLf & _
        "            and aaa <= c" & vbCrLf & _
        "    ) as tx" & vbCrLf & _
        "where" & vbCrLf & _
        "    a < b" & vbCrLf & _
        "    and aa <= b" & vbCrLf & _
        "    and aaa <= case" & vbCrLf & _
        "            when y then b" & vbCrLf & _
        "            else c" & vbCrLf & _
        "            end" & vbCrLf & _
        "    and aaaa between x and y" & vbCrLf & _
        "    and aa in (z1, z2, z3)"
    sqlStr = sqlStr & vbCrLf & _
        "    and case" & vbCrLf & _
        "        when y then b" & vbCrLf & _
        "        else c" & vbCrLf & _
        "    end" & vbCrLf & _
        "    <= case" & vbCrLf & _
        "        when y then b" & vbCrLf & _
        "        else c" & vbCrLf & _
        "    end"
    sqlStr = sqlStr & vbCrLf & _
        "and not exists (" & vbCrLf & _
        "    select *" & vbCrLf & _
        "    from " & vbCrLf & _
        "        (" & vbCrLf & _
        "            select *" & vbCrLf & _
        "            from table1 t1" & vbCrLf & _
        "            where" & vbCrLf & _
        "                x < b" & vbCrLf & _
        "                and x <= c" & vbCrLf & _
        "        ) as tx" & vbCrLf & _
        "    where" & vbCrLf & _
        "        a < b" & vbCrLf & _
        "        and aa <= c" & vbCrLf
    ' --- 这里测试右侧分组的对齐
    sqlStr = sqlStr & vbCrLf & _
        "        and bbb <= (" & vbCrLf & _
        "                      select *" & vbCrLf & _
        "                      where" & vbCrLf & _
        "                         g  = 1" & vbCrLf & _
        "                         and h <= 2" & vbCrLf & _
        "       )" & vbCrLf & _
        ")"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    ' 3. 检查结果
    Debug.Assert strArr.Count = 54
    Debug.Assert strArr.Item(0)  = "select"
    Debug.Assert strArr.Item(1)  = "    x1"
    Debug.Assert strArr.Item(2)  = "from"
    Debug.Assert strArr.Item(3)  = "    ("
    Debug.Assert strArr.Item(4)  = "        select"
    Debug.Assert strArr.Item(5)  = "            *"
    Debug.Assert strArr.Item(6)  = "        from"
    Debug.Assert strArr.Item(7)  = "            table1 t1"
    Debug.Assert strArr.Item(8)  = "        where"
    Debug.Assert strArr.Item(9)  = "                aa  < b"
    Debug.Assert strArr.Item(10) = "            and aaa <= c"
    Debug.Assert strArr.Item(11) = "    ) as tx"
    Debug.Assert strArr.Item(12) = "where"
    Debug.Assert strArr.Item(13) = "        a           < b"
    Debug.Assert strArr.Item(14) = "    and aa          <= b"
    Debug.Assert strArr.Item(15) = "    and aaa         <= case"
    Debug.Assert strArr.Item(16) = "                           when y then b"
    Debug.Assert strArr.Item(17) = "                           else c"
    Debug.Assert strArr.Item(18) = "                       end"
    Debug.Assert strArr.Item(19) = "    and aaaa        between x and y"
    Debug.Assert strArr.Item(20) = "    and aa          in (z1, z2, z3)"
    Debug.Assert strArr.Item(21) = "    and case"
    Debug.Assert strArr.Item(22) = "            when y then b"
    Debug.Assert strArr.Item(23) = "            else c"
    Debug.Assert strArr.Item(24) = "        end"
    Debug.Assert strArr.Item(25) = "        <="
    Debug.Assert strArr.Item(26) = "        case"
    Debug.Assert strArr.Item(27) = "            when y then b"
    Debug.Assert strArr.Item(28) = "            else c"
    Debug.Assert strArr.Item(29) = "        end"
    Debug.Assert strArr.Item(30) = "    and not exists ("
    Debug.Assert strArr.Item(31) = "            select"
    Debug.Assert strArr.Item(32) = "                *"
    Debug.Assert strArr.Item(33) = "            from"
    Debug.Assert strArr.Item(34) = "                ("
    Debug.Assert strArr.Item(35) = "                    select"
    Debug.Assert strArr.Item(36) = "                        *"
    Debug.Assert strArr.Item(37) = "                    from"
    Debug.Assert strArr.Item(38) = "                        table1 t1"
    Debug.Assert strArr.Item(39) = "                    where"
    Debug.Assert strArr.Item(40) = "                            x < b"
    Debug.Assert strArr.Item(41) = "                        and x <= c"
    Debug.Assert strArr.Item(42) = "                ) as tx"
    Debug.Assert strArr.Item(43) = "            where"
    Debug.Assert strArr.Item(44) = "                    a         < b"
    Debug.Assert strArr.Item(45) = "                and aa        <= c"
    Debug.Assert strArr.Item(46) = "                and bbb       <= ("
    Debug.Assert strArr.Item(47) = "                                     select"
    Debug.Assert strArr.Item(48) = "                                         *"
    Debug.Assert strArr.Item(49) = "                                     where"
    Debug.Assert strArr.Item(50) = "                                             g = 1"
    Debug.Assert strArr.Item(51) = "                                         and h <= 2"
    Debug.Assert strArr.Item(52) = "                                 )"
    Debug.Assert strArr.Item(53) = "        );"

    Debug.Assert formatter.getSqlLineSize = 54
End Sub

' 测试: 解析 sql 字符串 + 正常连接多行注释 + 各部分左侧的注释
Private Sub test_analyze_with_multiline_comment_and_section_left_comment()
    Dim config As SqlFormatterConfiguration
    Set config = New SqlFormatterConfiguration
    config.alignmentLevel = SqlAlignmentLevel.ALL_LEVEL
    config.alignmentPosition = SqlAlignmentPosition.OPERATOR_LEFT

    Dim formatter As SqlFormatter
    Set formatter = New SqlFormatter
    formatter.Init config

    Dim token As SqlToken
    Dim scanner As ObjArrayRefScanner
    Dim elem As ISqlFormatterElem
    Dim strArr As IPrimitiveArrayRef
    Dim sqlStr As String
    Dim sqlEnvs As IObjArrayRef

    ' ==============================
    ' 1. 测试: 所有 token 都被使用，并解析成一个作用域
    '    select + STR_NAME
    '    + from + STR_NAME
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = _
        "--comment01" & vbCrLf & _
        "--comment02" & vbCrLf & _
        "select      -- select comment" & vbCrLf & _
        "    x1," & vbCrLf & _
        "    case a" & vbCrLf & _
        "        when 123 then 'abc'" & vbCrLf & _
        "        else 'xyz'" & vbCrLf & _
        "    end" & vbCrLf & _
        " --comment03"
    sqlStr = sqlStr & vbCrLf & _
        "from -- from comment" & vbCrLf & _
        "--comment04" & vbCrLf & _
        "    (" & vbCrLf & _
        "        select *" & vbCrLf & _
        "        from table1 t1" & vbCrLf & _
        "        --comment05" & vbCrLf & _
        "        where" & vbCrLf & _
        "            aa < b" & vbCrLf & _
        "            and aaa <= c" & vbCrLf & _
        "    ) as tx" & vbCrLf & _
        "    -- after from comment"
    sqlStr = sqlStr & vbCrLf & _
        "left join -- join comment" & vbCrLf & _
        "table2 t2" & vbCrLf & _
        "on -- on comment" & vbCrLf & _
        "   t1.aa = t2.a1" & vbCrLf & _
        "and t1.bb = t2.b1" & vbCrLf & _
        "and t1.cc = t2.c1"
    sqlStr = sqlStr & vbCrLf & _
        "where -- where comment" & vbCrLf & _
        "    a < b" & vbCrLf & _
        "/*" & vbCrLf & _
        "1234" & vbCrLf & _
        "wxyz" & vbCrLf & _
        "*/" & vbCrLf & _
        "    and aa <= b" & vbCrLf & _
        "    and aaa <= case" & vbCrLf & _
        "            when y then b" & vbCrLf & _
        "            else c" & vbCrLf & _
        "            end" & vbCrLf & _
        "    and aaaa between x and y"
    sqlStr = sqlStr & vbCrLf & _
        "   /*" & vbCrLf & _
        "   aaaa" & vbCrLf & _
        "   bbbb" & vbCrLf & _
        "   cccc" & vbCrLf & _
        "   */" & vbCrLf & _
        "--comment05" & vbCrLf & _
        "order by -- orderby comment" & vbCrLf & _
        "t1.aa asc, t1.bb desc, t2.c1, t2.d1 desc" & vbCrLf & _
        "   /*" & vbCrLf & _
        "   aaaa2" & vbCrLf & _
        "   bbbb2" & vbCrLf & _
        "   cccc2" & vbCrLf & _
        "   */" & vbCrLf & _
        "--comment06" & vbCrLf & _
        "group by -- groupby comment" & vbCrLf & _
        "t1.aa, t1.bb, t2.c1, t2.d1" & vbCrLf & _
        "   /*" & vbCrLf & _
        "   aaaa3" & vbCrLf & _
        "   bbbb3" & vbCrLf & _
        "   cccc3" & vbCrLf & _
        "   */" & vbCrLf & _
        "--comment07"
    sqlStr = sqlStr & vbCrLf & _
        "union all     -- union comment" & vbCrLf & _
        "(" & vbCrLf & _
        "  select tx.a, tx.b, max(a, b, c)" & vbCrLf & _
        "  from tableX tx" & vbCrLf & _
        "-- after from comment2" & vbCrLf & _
        "  left join tableY ty" & vbCrLf & _
        "  on ty.aa = tx.a1" & vbCrLf & _
        "  and ty.bb <= tx.b1" & vbCrLf & _
        "  where tx.a = 1" & vbCrLf & _
        "  and tx.b like '%abc%'" & vbCrLf & _
        ")" & vbCrLf & _
        "--comment08"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    ' 3. 检查结果
    Debug.Assert strArr.Count = 89
    Debug.Assert strArr.Item(0)  = "--comment01"
    Debug.Assert strArr.Item(1)  = "--comment02"
    Debug.Assert strArr.Item(2)  = "select -- select comment"
    Debug.Assert strArr.Item(3)  = "    x1,"
    Debug.Assert strArr.Item(4)  = "    case"
    Debug.Assert strArr.Item(5)  = "        a"
    Debug.Assert strArr.Item(6)  = "        when 123 then 'abc'"
    Debug.Assert strArr.Item(7)  = "        else 'xyz'"
    Debug.Assert strArr.Item(8)  = "    end"
    Debug.Assert strArr.Item(9)  = "--comment03"
    Debug.Assert strArr.Item(10)  = "from -- from comment"
    Debug.Assert strArr.Item(11) = "    --comment04"
    Debug.Assert strArr.Item(12) = "    ("
    Debug.Assert strArr.Item(13) = "        select"
    Debug.Assert strArr.Item(14) = "            *"
    Debug.Assert strArr.Item(15) = "        from"
    Debug.Assert strArr.Item(16) = "            table1 t1"
    Debug.Assert strArr.Item(17) = "        --comment05"
    Debug.Assert strArr.Item(18) = "        where"
    Debug.Assert strArr.Item(19) = "                aa  < b"
    Debug.Assert strArr.Item(20) = "            and aaa <= c"
    Debug.Assert strArr.Item(21) = "    ) as tx"
    Debug.Assert strArr.Item(22) = "-- after from comment"
    Debug.Assert strArr.Item(23) = "left join -- join comment"
    Debug.Assert strArr.Item(24) = "    table2 t2"
    Debug.Assert strArr.Item(25) = "on -- on comment"
    Debug.Assert strArr.Item(26) = "        t1.aa       = t2.a1"
    Debug.Assert strArr.Item(27) = "    and t1.bb       = t2.b1"
    Debug.Assert strArr.Item(28) = "    and t1.cc       = t2.c1"
    Debug.Assert strArr.Item(29) = "where -- where comment"
    Debug.Assert strArr.Item(30) = "        a           < b"
    Debug.Assert strArr.Item(31) = "    /*"
    Debug.Assert strArr.Item(32) = "     1234"
    Debug.Assert strArr.Item(33) = "     wxyz"
    Debug.Assert strArr.Item(34) = "     */"
    Debug.Assert strArr.Item(35) = "    and aa          <= b"
    Debug.Assert strArr.Item(36) = "    and aaa         <= case"
    Debug.Assert strArr.Item(37) = "                           when y then b"
    Debug.Assert strArr.Item(38) = "                           else c"
    Debug.Assert strArr.Item(39) = "                       end"
    Debug.Assert strArr.Item(40) = "    and aaaa        between x and y"
    Debug.Assert strArr.Item(41) = "/*"
    Debug.Assert strArr.Item(42) = " aaaa"
    Debug.Assert strArr.Item(43) = " bbbb"
    Debug.Assert strArr.Item(44) = " cccc"
    Debug.Assert strArr.Item(45) = " */" 
    Debug.Assert strArr.Item(46) = "--comment05" 
    Debug.Assert strArr.Item(47) = "order by -- orderby comment"
    Debug.Assert strArr.Item(48) = "    t1.aa asc,"
    Debug.Assert strArr.Item(49) = "    t1.bb desc,"
    Debug.Assert strArr.Item(50) = "    t2.c1,"
    Debug.Assert strArr.Item(51) = "    t2.d1 desc"
    Debug.Assert strArr.Item(52) = "/*"
    Debug.Assert strArr.Item(53) = " aaaa2"
    Debug.Assert strArr.Item(54) = " bbbb2"
    Debug.Assert strArr.Item(55) = " cccc2"
    Debug.Assert strArr.Item(56) = " */" 
    Debug.Assert strArr.Item(57) = "--comment06" 
    Debug.Assert strArr.Item(58) = "group by -- groupby comment"
    Debug.Assert strArr.Item(59) = "    t1.aa,"
    Debug.Assert strArr.Item(60) = "    t1.bb,"
    Debug.Assert strArr.Item(61) = "    t2.c1,"
    Debug.Assert strArr.Item(62) = "    t2.d1"
    Debug.Assert strArr.Item(63) = "/*"
    Debug.Assert strArr.Item(64) = " aaaa3"
    Debug.Assert strArr.Item(65) = " bbbb3"
    Debug.Assert strArr.Item(66) = " cccc3"
    Debug.Assert strArr.Item(67) = " */" 
    Debug.Assert strArr.Item(68) = "--comment07"
    Debug.Assert strArr.Item(69) = "union all -- union comment"
    Debug.Assert strArr.Item(70) = "("
    Debug.Assert strArr.Item(71) = "    select"
    Debug.Assert strArr.Item(72) = "        tx.a,"
    Debug.Assert strArr.Item(73) = "        tx.b,"
    Debug.Assert strArr.Item(74) = "        max(a, b, c)"
    Debug.Assert strArr.Item(75) = "    from"
    Debug.Assert strArr.Item(76) = "        tableX tx"
    Debug.Assert strArr.Item(77) = "    -- after from comment2"
    Debug.Assert strArr.Item(78) = "    left join"
    Debug.Assert strArr.Item(79) = "        tableY ty"
    Debug.Assert strArr.Item(80) = "    on"
    Debug.Assert strArr.Item(81) = "            ty.aa   = tx.a1"
    Debug.Assert strArr.Item(82) = "        and ty.bb   <= tx.b1"
    Debug.Assert strArr.Item(83) = "    where"
    Debug.Assert strArr.Item(84) = "            tx.a    = 1"
    Debug.Assert strArr.Item(85) = "        and tx.b    like '%abc%'"
    Debug.Assert strArr.Item(86) = ")"
    Debug.Assert strArr.Item(87) = "--comment08"
    Debug.Assert strArr.Item(88) = ";"

    Debug.Assert formatter.getSqlLineSize = 89
End Sub

' 测试: 解析 update 的基础结构 -- 无对齐
Private Sub test_analyze_update_no_alignment()
    Dim config As SqlFormatterConfiguration
    Set config = New SqlFormatterConfiguration
    config.alignmentLevel = SqlAlignmentLevel.NO_ALIGNMENT

    Dim formatter As SqlFormatter
    Set formatter = New SqlFormatter
    formatter.Init config

    Dim token As SqlToken
    Dim scanner As ObjArrayRefScanner
    Dim elem As ISqlFormatterElem
    Dim strArr As IPrimitiveArrayRef
    Dim sqlStr As String
    Dim sqlEnvs As IObjArrayRef

    ' ==============================
    ' 1. 测试: 所有 token 都被使用，并解析成一个作用域
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = _
        "update ignore set " & vbCrLf & _
        "    a = 'a'," & vbCrLf & _
        "    bb = 'bb'," & vbCrLf & _
        "    ccc = 'ccc'," & vbCrLf & _
        "    dddd = 'dddd'"
    sqlStr = sqlStr & vbCrLf & _
        "where" & vbCrLf & _
        "    a < b" & vbCrLf & _
        "    and aa <= b" & vbCrLf & _
        "    and aaa <= case" & vbCrLf & _
        "            when y then b" & vbCrLf & _
        "            else c" & vbCrLf & _
        "            end" & vbCrLf & _
        "    and aaaa between x and y" & vbCrLf & _
        "    and aa in (z1, z2, z3)"
    sqlStr = sqlStr & vbCrLf & _
        "order by t1.aa asc, t2.bb desc, t3.cc" & vbCrLf & _
        "limit 12 offset 100"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    Debug.Assert strArr.Count = 21
    Debug.Assert strArr.Item(0)  = "update"
    Debug.Assert strArr.Item(1)  = "    ignore"
    Debug.Assert strArr.Item(2)  = "set"
    Debug.Assert strArr.Item(3)  = "    a = 'a',"
    Debug.Assert strArr.Item(4)  = "    bb = 'bb',"
    Debug.Assert strArr.Item(5)  = "    ccc = 'ccc',"
    Debug.Assert strArr.Item(6)  = "    dddd = 'dddd'"
    Debug.Assert strArr.Item(7)  = "where"
    Debug.Assert strArr.Item(8)  = "        a < b"
    Debug.Assert strArr.Item(9)  = "    and aa <= b"
    Debug.Assert strArr.Item(10) = "    and aaa <= case"
    Debug.Assert strArr.Item(11) = "                   when y then b"
    Debug.Assert strArr.Item(12) = "                   else c"
    Debug.Assert strArr.Item(13) = "               end"
    Debug.Assert strArr.Item(14) = "    and aaaa between x and y"
    Debug.Assert strArr.Item(15) = "    and aa in (z1, z2, z3)"
    Debug.Assert strArr.Item(16) = "order by"
    Debug.Assert strArr.Item(17) = "    t1.aa asc,"
    Debug.Assert strArr.Item(18) = "    t2.bb desc,"
    Debug.Assert strArr.Item(19) = "    t3.cc"
    Debug.Assert strArr.Item(20) = "limit 12 offset 100;"

    Debug.Assert formatter.getSqlLineSize = 21

End Sub

' 测试: 解析 update 的基础结构 + 每个条件分组全体对齐 + 以条件运算符的右侧为基准进行对齐
Private Sub test_analyze_update_all_localtion_right()
    Dim config As SqlFormatterConfiguration
    Set config = New SqlFormatterConfiguration
    config.alignmentLevel = SqlAlignmentLevel.ALL_LEVEL

    Dim formatter As SqlFormatter
    Set formatter = New SqlFormatter
    formatter.Init config

    Dim token As SqlToken
    Dim scanner As ObjArrayRefScanner
    Dim elem As ISqlFormatterElem
    Dim strArr As IPrimitiveArrayRef
    Dim sqlStr As String
    Dim sqlEnvs As IObjArrayRef

    ' ==============================
    ' 1. 测试: 所有 token 都被使用，并解析成一个作用域
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = _
        "with wa (wac1, wac2, wac3) as (select * from table01)," & vbCrLf & _
        "     wb (wbc1, wbc2, wbc3) as (" & vbCrLf & _
        "          select * from table01 t1" & vbCrLf & _
        "          left join table02 t2" & vbCrLf & _
        "          on t1.c1 = t2.c1" & vbCrLf & _
        "          and t1.c2 <= t2.c2" & vbCrLf & _
        "          where t1.c1 = 'xxx'" & vbCrLf & _
        "          and t2.c3 <= 1234" & vbCrLf & _
        "    )"
    sqlStr = sqlStr & vbCrLf & _
        "update ignore set " & vbCrLf & _
        "    a = 'a'," & vbCrLf & _
        "    bb = 'bb'," & vbCrLf & _
        "    ccc = (" & vbCrLf & _
        "       select * from table1" & vbCrLf & _
        "       where a = t1" & vbCrLf & _
        "       and bb <= t2" & vbCrLf & _
        "       and ccc <= t3" & vbCrLf & _
        "    )," & vbCrLf & _
        "    dddd = 'dddd'"
    sqlStr = sqlStr & vbCrLf & _
        "where" & vbCrLf & _
        "    a < b" & vbCrLf & _
        "    and aa <= b" & vbCrLf & _
        "    and aaa <= case" & vbCrLf & _
        "            when y then b" & vbCrLf & _
        "            else c" & vbCrLf & _
        "            end" & vbCrLf & _
        "    and aaaa between x and y" & vbCrLf & _
        "    and aa in (z1, z2, z3)"
    sqlStr = sqlStr & vbCrLf & _
        "order by t1.aa asc, t2.bb desc, t3.cc" & vbCrLf & _
        "limit 12 offset 100"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    Debug.Assert strArr.Count = 50

    Debug.Assert strArr.Item(0)  = "with wa (wac1, wac2, wac3) as ("
    Debug.Assert strArr.Item(1)  = "    select"
    Debug.Assert strArr.Item(2)  = "        *"
    Debug.Assert strArr.Item(3)  = "    from"
    Debug.Assert strArr.Item(4)  = "        table01"
    Debug.Assert strArr.Item(5)  = "),"
    Debug.Assert strArr.Item(6)  = "wb (wbc1, wbc2, wbc3) as ("
    Debug.Assert strArr.Item(7)  = "    select"
    Debug.Assert strArr.Item(8)  = "        *"
    Debug.Assert strArr.Item(9)  = "    from"
    Debug.Assert strArr.Item(10) = "        table01 t1"
    Debug.Assert strArr.Item(11) = "    left join"
    Debug.Assert strArr.Item(12) = "        table02 t2"
    Debug.Assert strArr.Item(13) = "    on"
    Debug.Assert strArr.Item(14) = "            t1.c1  = t2.c1"
    Debug.Assert strArr.Item(15) = "        and t1.c2 <= t2.c2"
    Debug.Assert strArr.Item(16) = "    where"
    Debug.Assert strArr.Item(17) = "            t1.c1  = 'xxx'"
    Debug.Assert strArr.Item(18) = "        and t2.c3 <= 1234"
    Debug.Assert strArr.Item(19) = ")"
    Debug.Assert strArr.Item(20) = "update"
    Debug.Assert strArr.Item(21) = "    ignore"
    Debug.Assert strArr.Item(22) = "set"
    Debug.Assert strArr.Item(23) = "    a    = 'a',"
    Debug.Assert strArr.Item(24) = "    bb   = 'bb',"
    Debug.Assert strArr.Item(25) = "    ccc  = ("
    Debug.Assert strArr.Item(26) = "               select"
    Debug.Assert strArr.Item(27) = "                   *"
    Debug.Assert strArr.Item(28) = "               from"
    Debug.Assert strArr.Item(29) = "                   table1"
    Debug.Assert strArr.Item(30) = "               where"
    Debug.Assert strArr.Item(31) = "                       a    = t1"
    Debug.Assert strArr.Item(32) = "                   and bb  <= t2"
    Debug.Assert strArr.Item(33) = "                   and ccc <= t3"
    Debug.Assert strArr.Item(34) = "           ),"
    Debug.Assert strArr.Item(35) = "    dddd = 'dddd'"
    Debug.Assert strArr.Item(36) = "where"
    Debug.Assert strArr.Item(37) = "        a          < b"
    Debug.Assert strArr.Item(38) = "    and aa        <= b"
    Debug.Assert strArr.Item(39) = "    and aaa       <= case"
    Debug.Assert strArr.Item(40) = "                         when y then b"
    Debug.Assert strArr.Item(41) = "                         else c"
    Debug.Assert strArr.Item(42) = "                     end"
    Debug.Assert strArr.Item(43) = "    and aaaa between x and y"
    Debug.Assert strArr.Item(44) = "    and aa        in (z1, z2, z3)"
    Debug.Assert strArr.Item(45) = "order by"
    Debug.Assert strArr.Item(46) = "    t1.aa asc,"
    Debug.Assert strArr.Item(47) = "    t2.bb desc,"
    Debug.Assert strArr.Item(48) = "    t3.cc"
    Debug.Assert strArr.Item(49) = "limit 12 offset 100;"

    Debug.Assert formatter.getSqlLineSize = 50
End Sub

' 测试: 解析 update 的基础结构 + 每个条件分组全体对齐 + 以条件运算符的左侧为基准进行对齐
Private Sub test_analyze_update_all_localtion_left()
    Dim config As SqlFormatterConfiguration
    Set config = New SqlFormatterConfiguration
    config.alignmentLevel = SqlAlignmentLevel.ALL_LEVEL
    config.alignmentPosition = SqlAlignmentPosition.OPERATOR_LEFT

    Dim formatter As SqlFormatter
    Set formatter = New SqlFormatter
    formatter.Init config

    Dim token As SqlToken
    Dim scanner As ObjArrayRefScanner
    Dim elem As ISqlFormatterElem
    Dim strArr As IPrimitiveArrayRef
    Dim sqlStr As String
    Dim sqlEnvs As IObjArrayRef

    ' ==============================
    ' 1. 测试: 所有 token 都被使用，并解析成一个作用域
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = _
        "update ignore set " & vbCrLf & _
        "    a = 'a'," & vbCrLf & _
        "    bb = 'bb'," & vbCrLf & _
        "    ccc = (" & vbCrLf & _
        "       select * from table1" & vbCrLf & _
        "       where a = t1" & vbCrLf & _
        "       and bb <= t2" & vbCrLf & _
        "       and ccc <= t3" & vbCrLf & _
        "    )," & vbCrLf & _
        "    dddd = 'dddd'"
    sqlStr = sqlStr & vbCrLf & _
        "where" & vbCrLf & _
        "    a < b" & vbCrLf & _
        "    and aa <= b" & vbCrLf & _
        "    and aaa <= case" & vbCrLf & _
        "            when y then b" & vbCrLf & _
        "            else c" & vbCrLf & _
        "            end" & vbCrLf & _
        "    and aaaa between x and y" & vbCrLf & _
        "    and aa in (z1, z2, z3)"
    sqlStr = sqlStr & vbCrLf & _
        "order by t1.aa asc, t2.bb desc, t3.cc" & vbCrLf & _
        "limit 12 offset 100"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    Debug.Assert strArr.Count = 30
    Debug.Assert strArr.Item(0)  = "update"
    Debug.Assert strArr.Item(1)  = "    ignore"
    Debug.Assert strArr.Item(2)  = "set"
    Debug.Assert strArr.Item(3)  = "    a    = 'a',"
    Debug.Assert strArr.Item(4)  = "    bb   = 'bb',"
    Debug.Assert strArr.Item(5)  = "    ccc  = ("
    Debug.Assert strArr.Item(6)  = "               select"
    Debug.Assert strArr.Item(7)  = "                   *"
    Debug.Assert strArr.Item(8)  = "               from"
    Debug.Assert strArr.Item(9)  = "                   table1"
    Debug.Assert strArr.Item(10) = "               where"
    Debug.Assert strArr.Item(11) = "                       a   = t1"
    Debug.Assert strArr.Item(12) = "                   and bb  <= t2"
    Debug.Assert strArr.Item(13) = "                   and ccc <= t3"
    Debug.Assert strArr.Item(14) = "           ),"
    Debug.Assert strArr.Item(15) = "    dddd = 'dddd'"
    Debug.Assert strArr.Item(16) = "where"
    Debug.Assert strArr.Item(17) = "        a    < b"
    Debug.Assert strArr.Item(18) = "    and aa   <= b"
    Debug.Assert strArr.Item(19) = "    and aaa  <= case"
    Debug.Assert strArr.Item(20) = "                    when y then b"
    Debug.Assert strArr.Item(21) = "                    else c"
    Debug.Assert strArr.Item(22) = "                end"
    Debug.Assert strArr.Item(23) = "    and aaaa between x and y"
    Debug.Assert strArr.Item(24) = "    and aa   in (z1, z2, z3)"
    Debug.Assert strArr.Item(25) = "order by"
    Debug.Assert strArr.Item(26) = "    t1.aa asc,"
    Debug.Assert strArr.Item(27) = "    t2.bb desc,"
    Debug.Assert strArr.Item(28) = "    t3.cc"
    Debug.Assert strArr.Item(29) = "limit 12 offset 100;"

    Debug.Assert formatter.getSqlLineSize = 30

End Sub

' 测试: 解析 delete 的基础结构 -- 无对齐
Private Sub test_analyze_delete_no_alignment()
    Dim config As SqlFormatterConfiguration
    Set config = New SqlFormatterConfiguration
    config.alignmentLevel = SqlAlignmentLevel.NO_ALIGNMENT

    Dim formatter As SqlFormatter
    Set formatter = New SqlFormatter
    formatter.Init config

    Dim token As SqlToken
    Dim scanner As ObjArrayRefScanner
    Dim elem As ISqlFormatterElem
    Dim strArr As IPrimitiveArrayRef
    Dim sqlStr As String
    Dim sqlEnvs As IObjArrayRef

    ' ==============================
    ' 1. 测试: 所有 token 都被使用，并解析成一个作用域
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = _
        "delete  --c1" & vbCrLf & _
        "    low_priority" & vbCrLf & _
        "    x1.*, x2.*" & vbCrLf & _
        "from table1 t1, table2 t2" & vbCrLf & _
        "where t1.a1 = 'abc'" & vbCrLf & _
        "and t2.a2 = 'xyz'"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    Debug.Assert strArr.Count = 10
    Debug.Assert strArr.Item(0) = "delete --c1"
    Debug.Assert strArr.Item(1) = "    low_priority"
    Debug.Assert strArr.Item(2) = "    x1.*,"
    Debug.Assert strArr.Item(3) = "    x2.*"
    Debug.Assert strArr.Item(4) = "from"
    Debug.Assert strArr.Item(5) = "    table1 t1,"
    Debug.Assert strArr.Item(6) = "    table2 t2"
    Debug.Assert strArr.Item(7) = "where"
    Debug.Assert strArr.Item(8) = "        t1.a1 = 'abc'"
    Debug.Assert strArr.Item(9) = "    and t2.a2 = 'xyz';"

    Debug.Assert formatter.getSqlLineSize = 10
End Sub

' 测试: 解析 delete 的基础结构 + 每个条件分组全体对齐 + 以条件运算符的右侧为基准进行对齐
Private Sub test_analyze_delete_all_localtion_right()
    Dim config As SqlFormatterConfiguration
    Set config = New SqlFormatterConfiguration
    config.alignmentLevel = SqlAlignmentLevel.ALL_LEVEL

    Dim formatter As SqlFormatter
    Set formatter = New SqlFormatter
    formatter.Init config

    Dim token As SqlToken
    Dim scanner As ObjArrayRefScanner
    Dim elem As ISqlFormatterElem
    Dim strArr As IPrimitiveArrayRef
    Dim sqlStr As String
    Dim sqlEnvs As IObjArrayRef

    ' ==============================
    ' 1. 测试: 所有 token 都被使用，并解析成一个作用域
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = _
        "delete  --c1" & vbCrLf & _
        "    low_priority" & vbCrLf & _
        "    x1.*, x2.*" & vbCrLf & _
        "from table1 t1, table2 t2" & vbCrLf & _
        "where t1.a1 <= 'abc'" & vbCrLf & _
        "and t2.a2 = 'xyz'"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    Debug.Assert strArr.Count = 10
    Debug.Assert strArr.Item(0) = "delete --c1"
    Debug.Assert strArr.Item(1) = "    low_priority"
    Debug.Assert strArr.Item(2) = "    x1.*,"
    Debug.Assert strArr.Item(3) = "    x2.*"
    Debug.Assert strArr.Item(4) = "from"
    Debug.Assert strArr.Item(5) = "    table1 t1,"
    Debug.Assert strArr.Item(6) = "    table2 t2"
    Debug.Assert strArr.Item(7) = "where"
    Debug.Assert strArr.Item(8) = "        t1.a1 <= 'abc'"
    Debug.Assert strArr.Item(9) = "    and t2.a2  = 'xyz';"

    Debug.Assert formatter.getSqlLineSize = 10
End Sub

' 测试: 解析 delete 的基础结构 + 每个条件分组全体对齐 + 以条件运算符的左侧为基准进行对齐
Private Sub test_analyze_delete_all_localtion_left()
    Dim config As SqlFormatterConfiguration
    Set config = New SqlFormatterConfiguration
    config.alignmentLevel = SqlAlignmentLevel.ALL_LEVEL
    config.alignmentPosition = SqlAlignmentPosition.OPERATOR_LEFT

    Dim formatter As SqlFormatter
    Set formatter = New SqlFormatter
    formatter.Init config

    Dim token As SqlToken
    Dim scanner As ObjArrayRefScanner
    Dim elem As ISqlFormatterElem
    Dim strArr As IPrimitiveArrayRef
    Dim sqlStr As String
    Dim sqlEnvs As IObjArrayRef

    ' ==============================
    ' 1. 测试: 所有 token 都被使用，并解析成一个作用域
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = _
        "delete  --c1" & vbCrLf & _
        "    low_priority" & vbCrLf & _
        "    x1.*, x2.*" & vbCrLf & _
        "from table1 t1, table2 t2" & vbCrLf & _
        "where t1.a1 <= 'abc'" & vbCrLf & _
        "and t2.bbb = 'xyz'"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    Debug.Assert strArr.Count = 10
    Debug.Assert strArr.Item(0) = "delete --c1"
    Debug.Assert strArr.Item(1) = "    low_priority"
    Debug.Assert strArr.Item(2) = "    x1.*,"
    Debug.Assert strArr.Item(3) = "    x2.*"
    Debug.Assert strArr.Item(4) = "from"
    Debug.Assert strArr.Item(5) = "    table1 t1,"
    Debug.Assert strArr.Item(6) = "    table2 t2"
    Debug.Assert strArr.Item(7) = "where"
    Debug.Assert strArr.Item(8) = "        t1.a1  <= 'abc'"
    Debug.Assert strArr.Item(9) = "    and t2.bbb = 'xyz';"

    Debug.Assert formatter.getSqlLineSize = 10
End Sub

' 测试: 解析 insert 的基础结构 -- 无对齐
Private Sub test_analyze_insert_no_alignment()
    Dim config As SqlFormatterConfiguration
    Set config = New SqlFormatterConfiguration
    config.alignmentLevel = SqlAlignmentLevel.NO_ALIGNMENT

    Dim formatter As SqlFormatter
    Set formatter = New SqlFormatter
    formatter.Init config

    Dim token As SqlToken
    Dim scanner As ObjArrayRefScanner
    Dim elem As ISqlFormatterElem
    Dim strArr As IPrimitiveArrayRef
    Dim sqlStr As String
    Dim sqlEnvs As IObjArrayRef

    ' ==============================
    ' 1. 测试: 所有 token 都被使用，并解析成一个作用域
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = _
        "insert --c1" & vbCrLf & _
        "low_priority ignore " & vbCrLf & _
        "into table1 (c1, c2, c3, c4, c5)" & vbCrLf & _
        "as newRow (x1, x2, x3, x4, x5)" & vbCrLf & _
        "values " & vbCrLf & _
        "    ('v11', 'v12', 'v13', 'v14', 'v15' )," & vbCrLf & _
        "    ('v21', 'v22', 'v23', 'v24', 'v25' )," & vbCrLf & _
        "    ('v31', 'v32', 'v33', 'v34', 'v35' )"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    Debug.Assert strArr.Count = 36
    Debug.Assert strArr.Item(0)  = "insert --c1"
    Debug.Assert strArr.Item(1)  = "    low_priority"
    Debug.Assert strArr.Item(2)  = "    ignore"
    Debug.Assert strArr.Item(3)  = "into"
    Debug.Assert strArr.Item(4)  = "    table1"
    Debug.Assert strArr.Item(5)  = "    ("
    Debug.Assert strArr.Item(6)  = "        c1,"
    Debug.Assert strArr.Item(7)  = "        c2,"
    Debug.Assert strArr.Item(8)  = "        c3,"
    Debug.Assert strArr.Item(9)  = "        c4,"
    Debug.Assert strArr.Item(10) = "        c5"
    Debug.Assert strArr.Item(11) = "    )"
    Debug.Assert strArr.Item(12) = "as"
    Debug.Assert strArr.Item(13) = "    newRow (x1, x2, x3, x4, x5)"
    Debug.Assert strArr.Item(14) = "values"
    Debug.Assert strArr.Item(15) = "    ("
    Debug.Assert strArr.Item(16) = "        'v11',"
    Debug.Assert strArr.Item(17) = "        'v12',"
    Debug.Assert strArr.Item(18) = "        'v13',"
    Debug.Assert strArr.Item(19) = "        'v14',"
    Debug.Assert strArr.Item(20) = "        'v15'"
    Debug.Assert strArr.Item(21) = "    ),"
    Debug.Assert strArr.Item(22) = "    ("
    Debug.Assert strArr.Item(23) = "        'v21',"
    Debug.Assert strArr.Item(24) = "        'v22',"
    Debug.Assert strArr.Item(25) = "        'v23',"
    Debug.Assert strArr.Item(26) = "        'v24',"
    Debug.Assert strArr.Item(27) = "        'v25'"
    Debug.Assert strArr.Item(28) = "    ),"
    Debug.Assert strArr.Item(29) = "    ("
    Debug.Assert strArr.Item(30) = "        'v31',"
    Debug.Assert strArr.Item(31) = "        'v32',"
    Debug.Assert strArr.Item(32) = "        'v33',"
    Debug.Assert strArr.Item(33) = "        'v34',"
    Debug.Assert strArr.Item(34) = "        'v35'"
    Debug.Assert strArr.Item(35) = "    );"

    Debug.Assert formatter.getSqlLineSize = 36

    ' ==============================
    ' 2. 测试: 插入值是一个子查询
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = _
        "insert --c1" & vbCrLf & _
        "low_priority ignore " & vbCrLf & _
        "into table1 (c1, c2, c3, c4, c5)"
    sqlStr = sqlStr & vbCrLf & _
        "select x1, x2, x3, x4, x5" & vbCrLf & _
        "from table1" & vbCrLf & _
        "where a1 = 'abc'" & vbCrLf & _
        "and a2 <= a3"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    Debug.Assert strArr.Count = 23
    Debug.Assert strArr.Item(0)  = "insert --c1"
    Debug.Assert strArr.Item(1)  = "    low_priority"
    Debug.Assert strArr.Item(2)  = "    ignore"
    Debug.Assert strArr.Item(3)  = "into"
    Debug.Assert strArr.Item(4)  = "    table1"
    Debug.Assert strArr.Item(5)  = "    ("
    Debug.Assert strArr.Item(6)  = "        c1,"
    Debug.Assert strArr.Item(7)  = "        c2,"
    Debug.Assert strArr.Item(8)  = "        c3,"
    Debug.Assert strArr.Item(9)  = "        c4,"
    Debug.Assert strArr.Item(10) = "        c5"
    Debug.Assert strArr.Item(11) = "    )"
    Debug.Assert strArr.Item(12) = "select"
    Debug.Assert strArr.Item(13) = "    x1,"
    Debug.Assert strArr.Item(14) = "    x2,"
    Debug.Assert strArr.Item(15) = "    x3,"
    Debug.Assert strArr.Item(16) = "    x4,"
    Debug.Assert strArr.Item(17) = "    x5"
    Debug.Assert strArr.Item(18) = "from"
    Debug.Assert strArr.Item(19) = "    table1"
    Debug.Assert strArr.Item(20) = "where"
    Debug.Assert strArr.Item(21) = "        a1 = 'abc'"
    Debug.Assert strArr.Item(22) = "    and a2 <= a3;"

    Debug.Assert formatter.getSqlLineSize = 23
End Sub

' 测试: 解析 insert 的基础结构 + 每个条件分组全体对齐 + 以条件运算符的右侧为基准进行对齐
Private Sub test_analyze_insert_all_localtion_right()
    Dim config As SqlFormatterConfiguration
    Set config = New SqlFormatterConfiguration
    config.alignmentLevel = SqlAlignmentLevel.ALL_LEVEL

    Dim formatter As SqlFormatter
    Set formatter = New SqlFormatter
    formatter.Init config

    Dim token As SqlToken
    Dim scanner As ObjArrayRefScanner
    Dim elem As ISqlFormatterElem
    Dim strArr As IPrimitiveArrayRef
    Dim sqlStr As String
    Dim sqlEnvs As IObjArrayRef

    ' ==============================
    ' 1. 只测试: 插入值是一个子查询 中的子查询内的条件可以对齐
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = _
        "insert --c1" & vbCrLf & _
        "low_priority ignore " & vbCrLf & _
        "into table1 (c1, c2, c3, c4, c5)"
    sqlStr = sqlStr & vbCrLf & _
        "select x1, x2, x3, x4, x5" & vbCrLf & _
        "from table1" & vbCrLf & _
        "where aaaa = 'abc'" & vbCrLf & _
        "and bb <= a3"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    Debug.Assert strArr.Count = 23
    Debug.Assert strArr.Item(0)  = "insert --c1"
    Debug.Assert strArr.Item(1)  = "    low_priority"
    Debug.Assert strArr.Item(2)  = "    ignore"
    Debug.Assert strArr.Item(3)  = "into"
    Debug.Assert strArr.Item(4)  = "    table1"
    Debug.Assert strArr.Item(5)  = "    ("
    Debug.Assert strArr.Item(6)  = "        c1,"
    Debug.Assert strArr.Item(7)  = "        c2,"
    Debug.Assert strArr.Item(8)  = "        c3,"
    Debug.Assert strArr.Item(9)  = "        c4,"
    Debug.Assert strArr.Item(10) = "        c5"
    Debug.Assert strArr.Item(11) = "    )"
    Debug.Assert strArr.Item(12) = "select"
    Debug.Assert strArr.Item(13) = "    x1,"
    Debug.Assert strArr.Item(14) = "    x2,"
    Debug.Assert strArr.Item(15) = "    x3,"
    Debug.Assert strArr.Item(16) = "    x4,"
    Debug.Assert strArr.Item(17) = "    x5"
    Debug.Assert strArr.Item(18) = "from"
    Debug.Assert strArr.Item(19) = "    table1"
    Debug.Assert strArr.Item(20) = "where"
    Debug.Assert strArr.Item(21) = "        aaaa = 'abc'"
    Debug.Assert strArr.Item(22) = "    and bb  <= a3;"

    Debug.Assert formatter.getSqlLineSize = 23
End Sub

' 测试: 解析 insert 的基础结构 + 每个条件分组全体对齐 + 以条件运算符的左侧为基准进行对齐
Private Sub test_analyze_insert_all_localtion_left()
    Dim config As SqlFormatterConfiguration
    Set config = New SqlFormatterConfiguration
    config.alignmentLevel = SqlAlignmentLevel.ALL_LEVEL
    config.alignmentPosition = SqlAlignmentPosition.OPERATOR_LEFT

    Dim formatter As SqlFormatter
    Set formatter = New SqlFormatter
    formatter.Init config

    Dim token As SqlToken
    Dim scanner As ObjArrayRefScanner
    Dim elem As ISqlFormatterElem
    Dim strArr As IPrimitiveArrayRef
    Dim sqlStr As String
    Dim sqlEnvs As IObjArrayRef

    ' ==============================
    ' 1. 只测试: 插入值是一个子查询 中的子查询内的条件可以对齐
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = _
        "insert --c1" & vbCrLf & _
        "low_priority ignore " & vbCrLf & _
        "into table1 (c1, c2, c3, c4, c5)"
    sqlStr = sqlStr & vbCrLf & _
        "select x1, x2, x3, x4, x5" & vbCrLf & _
        "from table1" & vbCrLf & _
        "where aaaa = 'abc'" & vbCrLf & _
        "and bb <= a3"
    sqlStr = sqlStr & vbCrLf & _
        "ON DUPLICATE KEY UPDATE" & vbCrLf & _
        "a = 'abc'," & vbCrLf & _
        "bb = 'xyz'," & vbCrLf & _
        "ccc = 'efg'"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    Debug.Assert strArr.Count = 27
    Debug.Assert strArr.Item(0)  = "insert --c1"
    Debug.Assert strArr.Item(1)  = "    low_priority"
    Debug.Assert strArr.Item(2)  = "    ignore"
    Debug.Assert strArr.Item(3)  = "into"
    Debug.Assert strArr.Item(4)  = "    table1"
    Debug.Assert strArr.Item(5)  = "    ("
    Debug.Assert strArr.Item(6)  = "        c1,"
    Debug.Assert strArr.Item(7)  = "        c2,"
    Debug.Assert strArr.Item(8)  = "        c3,"
    Debug.Assert strArr.Item(9)  = "        c4,"
    Debug.Assert strArr.Item(10) = "        c5"
    Debug.Assert strArr.Item(11) = "    )"
    Debug.Assert strArr.Item(12) = "select"
    Debug.Assert strArr.Item(13) = "    x1,"
    Debug.Assert strArr.Item(14) = "    x2,"
    Debug.Assert strArr.Item(15) = "    x3,"
    Debug.Assert strArr.Item(16) = "    x4,"
    Debug.Assert strArr.Item(17) = "    x5"
    Debug.Assert strArr.Item(18) = "from"
    Debug.Assert strArr.Item(19) = "    table1"
    Debug.Assert strArr.Item(20) = "where"
    Debug.Assert strArr.Item(21) = "        aaaa = 'abc'"
    Debug.Assert strArr.Item(22) = "    and bb   <= a3"
    Debug.Assert strArr.Item(23) = "ON DUPLICATE KEY UPDATE"
    Debug.Assert strArr.Item(24) = "    a   = 'abc',"
    Debug.Assert strArr.Item(25) = "    bb  = 'xyz',"
    Debug.Assert strArr.Item(26) = "    ccc = 'efg';"

    Debug.Assert formatter.getSqlLineSize = 27
End Sub

' 测试: 解析 insert 的基础结构 + 列的逗号后面不需要换行
Private Sub test_analyze_insert_colList_not_lineBreakAfterComma()
    Dim config As SqlFormatterConfiguration
    Set config = New SqlFormatterConfiguration
    config.alignmentLevel = SqlAlignmentLevel.NO_ALIGNMENT
    ' 设置配置，列的逗号后面不需要换行
    config.insertColListNeedLineBreakAfterComma = False

    Dim formatter As SqlFormatter
    Set formatter = New SqlFormatter
    formatter.Init config

    Dim token As SqlToken
    Dim scanner As ObjArrayRefScanner
    Dim elem As ISqlFormatterElem
    Dim strArr As IPrimitiveArrayRef
    Dim sqlStr As String
    Dim sqlEnvs As IObjArrayRef

    ' ==============================
    ' 1. 测试: 所有 token 都被使用，并解析成一个作用域
    ' ==============================
    ' 1. 创建 sql 字符串
    sqlStr = _
        "insert --c1" & vbCrLf & _
        "low_priority ignore " & vbCrLf & _
        "into table1 (c1, c2, c3, c4, c5)" & vbCrLf & _
        "as newRow (x1, x2, x3, x4, x5)" & vbCrLf & _
        "values " & vbCrLf & _
        "    ('v11', 'v12', 'v13', 'v14', 'v15' )," & vbCrLf & _
        "    ('v21', 'v22', 'v23', 'v24', 'v25' )," & vbCrLf & _
        "    ('v31', 'v32', 'v33', 'v34', 'v35' )"

    ' 2. 执行测试方法
    formatter.analyze sqlStr
    Set strArr = formatter.getSqlStrArray

    Debug.Assert strArr.Count = 12
    Debug.Assert strArr.Item(0)  = "insert --c1"
    Debug.Assert strArr.Item(1)  = "    low_priority"
    Debug.Assert strArr.Item(2)  = "    ignore"
    Debug.Assert strArr.Item(3)  = "into"
    Debug.Assert strArr.Item(4)  = "    table1"
    Debug.Assert strArr.Item(5)  = "    (c1, c2, c3, c4, c5)"
    Debug.Assert strArr.Item(6)  = "as"
    Debug.Assert strArr.Item(7)  = "    newRow (x1, x2, x3, x4, x5)"
    Debug.Assert strArr.Item(8)  = "values"
    Debug.Assert strArr.Item(9)  = "    ('v11', 'v12', 'v13', 'v14', 'v15'),"
    Debug.Assert strArr.Item(10) = "    ('v21', 'v22', 'v23', 'v24', 'v25'),"
    Debug.Assert strArr.Item(11) = "    ('v31', 'v32', 'v33', 'v34', 'v35');"

    Debug.Assert formatter.getSqlLineSize = 12
End Sub